package com.blog.dao.frontpage;

import java.util.List;

import com.blog.dao.conn.BaseDao;
import com.blog.dao.conn.ParamSet;
import com.blog.entity.ArticleCountEntity;
import com.blog.entity.ArticleDetailEntity;
import com.blog.entity.BlogPostEntity;
import com.blog.entity.BolgerArticleEntity;
import com.blog.entity.CommentContentEntity;
import com.blog.entity.DiscussEntity;
import com.blog.entity.DraftEntity;
import com.blog.entity.PersonalcenterDetailEntity;
import com.blog.entity.UserEntity;
import com.blog.entity.UserUrlEntity;
/**
 * 个人中心页面dao层
 * @author song
 *
 */
public class PersoncenterDao extends BaseDao{
	
	private static PersoncenterDao instance;
	private PersoncenterDao() {
		
	}
	public static synchronized PersoncenterDao getInstance() {
		if(instance==null) {
			instance=new PersoncenterDao();
		}
		return instance;
	}
	/**
	 * 1.根据作者id查询作者文章的详细信息
	 */
	public List<BolgerArticleEntity> getArticleInfoByWriterId(Integer id,int start,int length){
		String sql="select a.article_id,article_title,article_time,article_readnumber,article_content,user_name,count(discuss_id) comment_count\r\n" + 
				"from tb_article a LEFT JOIN tb_user u on  a.Writer_id=u.user_id\r\n" + 
				"left JOIN tb_discuss d on a.article_id=d.article_id where  writer_id=? group by a.article_id order by article_time desc limit ?,? ";
		return queryList(sql,new ParamSet(id,start,length),BolgerArticleEntity.class);
	}
	/**
	 * 2.将发布文章保存到数据库
	 */
	public void saveBlog(BlogPostEntity post) {
		String sql="insert into tb_article(article_title,article_time,article_content,Article_typesid,article_status,writer_id) values(?,?,?,?,?,?)";
		updateExecuted(sql, new ParamSet(post.getTitle(),post.getTime(),post.getContent(),post.getTypes(),post.getStatus(),post.getWriterid()));
	}
	/**
	 * 3.根据作者id查询草稿箱
	 */
	public List<DraftEntity> getDraftById(Integer id){
		String sql="select a.article_id,article_title,article_time,article_readnumber,count(*) article_discussnumber,c.discuss_id\r\n" + 
				"from tb_article a\r\n" + 
				"left join tb_discuss c\r\n" + 
				"on a.article_id=c.article_id\r\n" + 
				"where a.writer_id=? and a.article_status=1 \r\n" + 
				"group by a.article_id";
		return queryList(sql,new ParamSet(id),DraftEntity.class);
	}
	/**
	 * 4.根据作者id和文章id以及文章状态查询文章详细信息
	 */
	public PersonalcenterDetailEntity getBlogDetail(Integer id,Integer articleid,int status){
		String sql="select article_title,article_readnumber,article_supportnumber,count(*) article_discussnumber,c.discuss_id,article_content,types_name\r\n" + 
				"from tb_article a\r\n" + 
				"left join tb_discuss c\r\n" + 
				"on a.article_id=c.article_id\r\n" + 
				"LEFT JOIN tb_types t\r\n" + 
				"on a.article_typesid=t.types_id\r\n" + 
				"where a.writer_id=? and a.article_status=? and a.article_id=?\r\n" + 
				"group by a.article_id";
		return query(sql,new ParamSet(id,status,articleid), PersonalcenterDetailEntity.class);
	}
	/**
	 * 5.根据id查询相应博客总数目
	 */
	public ArticleCountEntity getTotalNum(Integer id,Integer status) {
		String sql="select count(*) totalNum from tb_article where writer_id=? and article_status=?";
		return query(sql,new ParamSet(id,status),ArticleCountEntity.class);
	}
	/**
	 * 6.根据博客id删除博客
	 */
	public void deleteBlogById(int id) {
		String sql="update tb_article article_status=2 where article_id=?";
		updateExecuted(sql,new ParamSet(id));
	}
	/**
	 * 根据用户id修改用户表相应的字段值
	 */
	public void updateUserInfo(String field,String value,int id) {
		StringBuffer sql=new StringBuffer("update tb_user set ");
		sql.append(field).append("='").append(value).append("' where user_id=").append(id);
		System.out.println(sql);
		updateExecuted(sql.toString());
	}
	/**
	 * 8.根据用户id查询用户信息
	 */
	public UserEntity getUserInfo(int id) {
		String sql="select * from tb_user where user_id=?";
		return query(sql,new ParamSet(id), UserEntity.class);
	}
	/**
	 * 9.根据用户id查询用户头像路径
	 */
	public UserUrlEntity getUserUrl(int id) {
		String sql="select user_image from tb_user where user_id=?";
		return query(sql,new ParamSet(id),UserUrlEntity.class);
	}
	/**
	 * 10.根据用户id保存图片路径
	 */
	public void saveHeadPic(String url,int id) {
		String sql="update tb_user set user_image='"+url+"' where user_id=?";
		updateExecuted(sql,new ParamSet(id));
	}
	/**
	 * 11.根据文章编号查询评论
	 */
	public List<CommentContentEntity> getComentById(int articleid){
		String sql="select d.discuss_content,d.discuss_time,u.user_name,d.discuss_numbering,d.discuss_id,d.article_id\r\n" + 
				"from tb_discuss d\r\n" + 
				"left join tb_user u on u.user_id=d.discuss_id where d.article_id=?";
		return queryList(sql,new ParamSet(articleid),CommentContentEntity.class);
	}
	/**
	 * 12.保存评论
	 */
	public void save(DiscussEntity de) {
		String sql="insert tb_discuss(discuss_id,article_id,discuss_content,discuss_time) values(?,?,?,?)";
		updateExecuted(sql,new ParamSet(de.getDiscuss_id(),de.getArticle_id(),de.getDiscuss_content(),de.getDiscuss_time()));
	}
	/**
	 * 根据文章编号查询博客的详细信息以及上一篇和下一篇的 信息
	 */
	public ArticleDetailEntity getArticleInfo(int blogid,int writerid){
		/*String sql="select a.article_id,article_title,article_time,article_readnumber,article_content,article_supportnumber,user_name,user_image,user_id,types_name,count(discuss_id) comment_count\r\n" + 
				"from tb_article a\r\n" + 
				"left JOIN tb_user u on u.user_id=a.writer_id\r\n" + 
				"left JOIN tb_types t on a.article_typesid=t.types_id\r\n" + 
				"left JOIN tb_discuss d on d.article_id=a.article_id\r\n" + 
				"where a.article_id in \r\n" + 
				"(select \r\n" + 
				"	case \r\n" + 
				"	when sign(article_id-?)<0 then max(article_id)\r\n" + 
				"	when sign(article_id-?)>0 then min(article_id)\r\n" + 
				"	ELSE	\r\n" + 
				"			article_id\r\n" + 
				"	end as article_id\r\n" + 
				"	from tb_article \r\n" + 
				"	GROUP BY\r\n" + 
				"       SIGN(article_id - ?)\r\n" + 
				"  ORDER BY\r\n" + 
				"       SIGN(article_id - ?)\r\n" + 
				") and a.writer_id=?\r\n" + 
				"GROUP BY a.article_id";
				*/
		/*String sql="(select a.article_id,article_title,article_time,article_readnumber,article_content,article_supportnumber,user_name,user_image,user_id,types_name,count(discuss_id) comment_count\r\n" + 
				"from tb_article a\r\n" + 
				"left JOIN tb_user u on u.user_id=a.writer_id\r\n" + 
				"left JOIN tb_types t on a.article_typesid=t.types_id\r\n" + 
				"left JOIN tb_discuss d on d.article_id=a.article_id\r\n" + 
				"where a.article_id<3 and a.writer_id=3\r\n" + 
				"GROUP BY a.article_id \r\n" + 
				" order by a.article_id desc limit 1) \r\n" + 
				"union ALL\r\n" + 
				"(select a.article_id,article_title,article_time,article_readnumber,article_content,article_supportnumber,user_name,user_image,user_id,types_name,count(discuss_id) comment_count\r\n" + 
				"from tb_article a\r\n" + 
				"left JOIN tb_user u on u.user_id=a.writer_id\r\n" + 
				"left JOIN tb_types t on a.article_typesid=t.types_id\r\n" + 
				"left JOIN tb_discuss d on d.article_id=a.article_id\r\n" + 
				"where a.article_id=3 and a.writer_id=3\r\n" + 
				"GROUP BY a.article_id \r\n" + 
				" order by a.article_id) \r\n" + 
				"union ALL\r\n" + 
				"(select a.article_id,article_title,article_time,article_readnumber,article_content,article_supportnumber,user_name,user_image,user_id,types_name,count(discuss_id) comment_count\r\n" + 
				"from tb_article a\r\n" + 
				"left JOIN tb_user u on u.user_id=a.writer_id\r\n" + 
				"left JOIN tb_types t on a.article_typesid=t.types_id\r\n" + 
				"left JOIN tb_discuss d on d.article_id=a.article_id\r\n" + 
				"where a.article_id>3 and a.writer_id=3\r\n" + 
				"GROUP BY a.article_id \r\n" + 
				" order by a.article_id limit 1)";*/
		String sql="select * from (select a.article_id,article_title,article_time,article_readnumber,article_content,article_supportnumber,user_name,user_image,user_id,types_name,count(discuss_id) comment_count\r\n" + 
				"from tb_article a\r\n" + 
				"left JOIN tb_user u on u.user_id=a.writer_id\r\n" + 
				"left JOIN tb_types t on a.article_typesid=t.types_id\r\n" + 
				"left JOIN tb_discuss d on d.article_id=a.article_id\r\n" + 
				"where a.article_id="+blogid+" and writer_id="+writerid+"\r\n" + 
				"GROUP BY a.article_id \r\n" + 
				"order by a.article_id) a\r\n" + 
				"left join (select article_id as prev,article_title as prev_title from tb_article where article_id>"+blogid+" and writer_id="+writerid+" ORDER BY prev LIMIT 1 ) b on a.article_id<b.prev left JOIN (select article_id as next,article_title as next_title from tb_article where article_id<"+blogid+" and writer_id="+writerid+" order by next DESC LIMIT 1 ) c on a.article_id>c.next";
		return query(sql,ArticleDetailEntity.class);
	}
	/**
	 * 4.根据作者id查询发表博客
	 */
	public List<DraftEntity> getPostBlogById(Integer id){
		String sql="select a.article_id,article_title,article_time,article_readnumber,count(*) article_discussnumber,c.discuss_id\r\n" + 
				"from tb_article a\r\n" + 
				"left join tb_discuss c\r\n" + 
				"on a.article_id=c.article_id\r\n" + 
				"where a.writer_id=? and a.article_status=0 \r\n" + 
				"group by a.article_id";
		return queryList(sql,new ParamSet(id),DraftEntity.class);
	}
	
}
